﻿CREATE PROCEDURE [util].[RefreshWaitStats]

AS
BEGIN

	DECLARE @RC int;
	SET @RC = 0;
	    
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	BEGIN TRAN;
	
    BEGIN TRY
    
		DECLARE @BaselineDate datetime;
		SET @BaselineDate = CURRENT_TIMESTAMP;

		TRUNCATE TABLE [util].[WaitStatsBaseline];
		INSERT INTO [util].[WaitStatsBaseline]
		(
			[BaselineDate]
			,[WaitType]
			,[WaitingTasksCount]
			,[WaitTimeMS]
			,[SignalWaitTimeMS]
		)
		SELECT
			@BaselineDate
			,[wait_type]
			,[waiting_tasks_count]
			,[wait_time_ms]
			,[signal_wait_time_ms]
		FROM sys.dm_os_wait_stats;
		
		TRUNCATE TABLE [util].[ProcessStatsBaseline];
		INSERT INTO [util].[ProcessStatsBaseline]
		(
			[BaselineDate]
			,[SessionID]
			,[LoginTime]
			,[HostName]
			,[ProgramName]
			,[HostProcessID]
			,[CPUTime]
			,[TotalScheduledTime]
			,[TotalElapsedTime]
			,[Reads]
			,[Writes]
			,[LogicalReads]
			,[ISUserProcess]
		)
		SELECT
			@BaselineDate
			,[session_id]
			,[login_time]
			,[host_name]
			,[program_name]
			,[host_process_id]
			,[cpu_time]
			,[total_scheduled_time]
			,[total_elapsed_time]
			,[reads]
			,[writes]
			,[logical_reads]
			,[is_user_process]
		FROM sys.dm_exec_sessions;
	END TRY
	BEGIN CATCH
		SET @RC = ERROR_NUMBER();
		DECLARE @ErrorMessage nvarchar(2048);
		SET @ErrorMessage = ERROR_MESSAGE();
		
		ROLLBACK TRAN;
		
		RAISERROR('Failed to update baseline: %s',16,1,@ErrorMessage);
	END CATCH

	COMMIT TRAN;
	
	RETURN(@RC);

END;